import sqlite3
import pandas as pd
import json
import openpyxl
from openpyxl.styles import PatternFill


yellow_fill = PatternFill(patternType="solid", fgColor="FFFF00")
lr_chinese = ["左眼", "右眼"]

yellow_columns_lr = ["AL", "KM", "CCTACD", "WTW/PS"]
yellow_columns = ["AL/CR PD"]


def format_xlsx(excel_path, space=3):
    """
    左对齐
    根据列数据自动调整列宽
    """

    from openpyxl import load_workbook
    from openpyxl.utils import get_column_letter
    from openpyxl.styles import Alignment

    # 使用 openpyxl 加载 Excel 文件
    wb = load_workbook(excel_path)
    sheet = wb.active
    align_left = Alignment(horizontal="left")

    # first_row_data = list(sheet.iter_rows(min_row=1, max_row=1, values_only=True))
    # column_names = [cell for cell in first_row_data[0]]

    # 获取首行值
    header_row = sheet[1]

    # 将 yellow_columns_lr 和 yellow_columns 填充为黄色
    for yellow_column in yellow_columns_lr:
        for lr in lr_chinese:
            # 查找列名对应的列索引
            column_name = f"{yellow_column}{lr}"
            for cell in header_row:
                if cell.value == column_name:
                    column_index = cell.column
                    break

            # 遍历指定列的所有单元格并设置填充颜色为黄色
            for row in sheet.iter_rows(min_col=column_index, max_col=column_index, min_row=1, max_row=sheet.max_row):
                for cell in row:
                    cell.fill = yellow_fill

    for yellow_column in yellow_columns:
        # 查找列名对应的列索引
        column_name = f"{yellow_column}"
        for cell in header_row:
            if cell.value == column_name:
                column_index = cell.column
                break

        # 遍历指定列的所有单元格并设置填充颜色为黄色
        for row in sheet.iter_rows(min_col=column_index, max_col=column_index, min_row=1, max_row=sheet.max_row):
            for cell in row:
                cell.fill = yellow_fill

    # 自动调整列宽
    for column in sheet.columns:
        max_length = 0
        column_ = column[0].column  # 获取列号
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = max_length + space  # 加space是为了给一些额外的空间
        sheet.column_dimensions[get_column_letter(column_)].width = adjusted_width

    # 遍历工作表中的所有单元格，并设置左对齐
    for row in sheet.iter_rows():
        for cell in row:
            cell.alignment = align_left

    # 冻结第1行和前5列
    sheet.freeze_panes = "G2" # G2上面和左边的都会被冻结

    wb.save(excel_path)


def get_data_from_sqlite(file):
    # 连接到SQLite数据库
    conn = sqlite3.connect(file)  # 替换为你的数据库文件名

    # 执行SQL查询以获取数据
    query = "SELECT * FROM my_table"  # 替换为你的表名
    df_raw = pd.read_sql_query(query, conn)

    # 关闭数据库连接
    conn.close()
    return df_raw


def add_column_value(column_name, df, value):
    pass


def format_dataframe(df_raw):
    step_x2mm = 0.0025  # 计算PD用, 0.0025为config中的step_x2mm

    df = pd.DataFrame({})  # 新建空的DataFrame

    df["序号"] = df_raw["id"]
    df["ID"] = df_raw["patient_id"]
    df["姓名"] = df_raw["name"]
    df["生日"] = df_raw["birthday"]
    df["性别"] = df_raw["sex"].replace({"male": "男", "female": "女"})
    df["时间"] = df_raw["timestamp"]

    # 读取所有数据, 转为json格式
    json_datas = []
    for index, row in df_raw.iterrows():
        data = row.data
        json_datas.append(json.loads(data))

    """
    item与measure data的key一致
    """

    # region AL
    item = "AL"
    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        df[f"{item}{lr_chinese}眼"] = pd.NA

        # separator = f"{item}{lr_chinese}眼"
        # df[separator] = pd.NA
        # columns_list = df.columns.tolist()
        # separator_index = columns_list.index(separator)

        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            aver = json_data[lr].get(f"{item}_AVG")
            if aver is None:
                continue

            values = json_data[lr].get(item)
            for n, value in enumerate(values):
                column_name = f"{item}{lr_chinese}{n}"
                if column_name not in df.columns:
                    df[column_name] = pd.NA
                df.at[i_row, column_name] = value[0]  # 数据

            column_name = f"{item}{lr_chinese}ADD"
            if column_name not in df.columns:
                df[column_name] = pd.NA
            df.at[i_row, column_name] = aver[0]

    # endregion

    # region KM
    item = "KM"
    names = ["R1", "R2", "A", "D1", "D2"]

    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        df[f"{item}{lr_chinese}眼"] = pd.NA

        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            aver = json_data[lr].get(f"{item}_AVG")
            if aver is None:
                continue

            for i, name in enumerate(names):
                column_name = f"{name}{lr_chinese}AVG"
                if column_name not in df.columns:
                    df[column_name] = pd.NA
                df.at[i_row, column_name] = aver[i]

            values = json_data[lr].get(item)
            for i, name in enumerate(names):
                for n, value in enumerate(values):
                    column_name = f"{name}{lr_chinese}{n}"
                    if column_name not in df.columns:
                        df[column_name] = pd.NA
                    df.at[i_row, column_name] = value[i]
    # endregion

    # region CCTACD
    item = "CCTACD"
    names = ["CCT", "AD"]
    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        df[f"{item}{lr_chinese}眼"] = pd.NA

        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            aver = json_data[lr].get(f"{item}_AVG")
            if aver is None:
                continue

            for i, name in enumerate(names):
                column_name = f"{name}{lr_chinese}AVG"
                if column_name not in df.columns:
                    df[column_name] = pd.NA
                df.at[i_row, column_name] = aver[i]

            values = json_data[lr].get(item)
            for i, name in enumerate(names):
                for n, value in enumerate(values):
                    column_name = f"{name}{lr_chinese}{n}"
                    if column_name not in df.columns:
                        df[column_name] = pd.NA
                    df.at[i_row, column_name] = value[i]
    # endregion

    # region CCTACD
    item = "CCTACD"
    names = ["CCT", "AD"]
    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        df[f"{item}{lr_chinese}眼"] = pd.NA

        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            aver = json_data[lr].get(f"{item}_AVG")
            if aver is None:
                continue

            for i, name in enumerate(names):
                column_name = f"{name}{lr_chinese}AVG"
                if column_name not in df.columns:
                    df[column_name] = pd.NA
                df.at[i_row, column_name] = aver[i]

            values = json_data[lr].get(item)
            for i, name in enumerate(names):
                for n, value in enumerate(values):
                    column_name = f"{name}{lr_chinese}{n}"
                    if column_name not in df.columns:
                        df[column_name] = pd.NA
                    df.at[i_row, column_name] = value[i]
    # endregion

    # region WTW/PS
    item = "WTW/PS"
    _items = ["WTW", "PS", "PSBright"]  # 与 data的key一致
    names = ["WTW", "PS暗室", "PS明室"]  # 显示表头
    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        df[f"{item}{lr_chinese}眼"] = pd.NA

        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            for i, _item in enumerate(_items):
                value = json_data[lr].get(_item)
                if value is None:
                    continue

                column_name = f"{names[i]}{lr_chinese}"
                if column_name not in df.columns:
                    df[column_name] = pd.NA
                df.at[i_row, column_name] = value[2]  # 左边界像素, 右边界像素, 计算后的数据
    # endregion

    # region AL/CR PD
    item = "AL/CR PD"
    df[item] = pd.NA

    _item = "AL/R"
    name = "AL/CR"
    for lr in ["left", "right"]:
        lr_chinese = "左" if lr == "left" else "右"
        for i_row, json_data in enumerate(json_datas):
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            value = json_data[lr].get(_item)
            if value is None:
                continue

            column_name = f"{name}{lr_chinese}"
            if column_name not in df.columns:
                df[column_name] = pd.NA
            df.at[i_row, column_name] = value

    _item = "PD"
    name = "PD"
    for i_row, json_data in enumerate(json_datas):
        boundary = []
        for lr in ["left", "right"]:
            lr_data = json_data.get(lr)
            if lr_data is None:
                continue

            value = json_data[lr].get(_item)
            if value is None:
                continue
            boundary.append(value[0])

        if len(boundary) == 2:
            column_name = f"{name}"
            if column_name not in df.columns:
                df[column_name] = pd.NA
            df.at[i_row, column_name] = abs(boundary[1] - boundary[0]) * step_x2mm

    # endregion

    # json_data["left"].get("KM")

    # # 新增空列
    # df["KM(左)"] = pd.NA
    # df["KM(右)"] = pd.NA

    # for index, row in df_raw.iterrows():
    #     data = row.data
    #     json_data = json.loads(data)
    #     df.at[index, "KM(左)"] = json_data["left"].get("KM")[0][0]
    #     # df.loc[index]["KM(左)"] = json_data["left"].get("KM")[0][0]

    return df


###########################################
import numpy as np

a = int(np.ceil(0.1))

if __name__ == "__main__":
    import os

    DownloadPath = "download"
    DatabaseFile = "record.db"

    remote_database_file = f"/home/pi/app/{DatabaseFile}"
    local_file = f"export.xlsx"

    database_file = os.path.join(DownloadPath, DatabaseFile)
    df = get_data_from_sqlite(database_file)
    df = format_dataframe(df)
    df.to_excel(local_file, index=False)
    format_xlsx(local_file)
    print(f"数据已成功导出到 {local_file}")

    # ###################################
    # data = """
    # {
    # "left": {"is_human": true, "PD": [-10000], "KM": [["9.47", "9.30", "129", "35.64", "36.29"], ["9.47", "9.30", "129", "35.64", "36.29"], ["9.47", "9.30", "129", "35.64", "36.29"]], "KM_AVG": ["9.47", "9.30", "129", "35.64", "36.29"], "CCTACD_FILE": ["/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_39_left_CCTACD[0].png", "/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_39_left_CCTACD[1].png", "/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_39_left_CCTACD[2].png"], "CCTACD": [["477", "3.47", "3.82"], ["475", "3.50", ""], ["480", "3.52", ""]], "CCTACD_AVG": ["477", "3.50", "3.82"], "PS": ["232", "433", "6.18"], "WTW": ["161", "534", "11.46"], "WTW_FILE": "/home/pi/Pictures/2024_08_08/WTW/[5567]14_06_39_left_WTW.png", "AL": [["27.31", "0.39", "12.45"], ["27.28", "0.12", "10.12"], ["27.30", "0.42", "12.97"], ["27.28", "0.12", "10.32"], ["27.28", "0.23", "12.72"], ["27.28", "0.34", "12.76"]], "AL_AVG": ["27.30", "0.19", "11.61"], "IC": [["9.15", "9.20", "9.25"]], "IC_AVG": ["9.20"], "AL/R": "2.91", "VT": "19.50"},
    # "right": {"is_human": true, "PD": [10000], "KM": [["8.62", "8.47", "137", "39.15", "39.85"], ["8.61", "8.46", "137", "39.20", "39.89"], ["8.62", "8.48", "137", "39.15", "39.80"]], "KM_AVG": ["8.62", "8.47", "137", "39.17", "39.85"], "CCTACD_FILE": ["/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_36_right_CCTACD[0].png", "/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_36_right_CCTACD[1].png", "/home/pi/Pictures/2024_08_08/CCTACD/[5567]14_06_36_right_CCTACD[2].png"], "CCTACD": [["520", "2.62", "4.13"], ["509", "2.64", ""], ["513", "2.60", ""]], "CCTACD_AVG": ["514", "2.62", "4.13"], "PS": ["218", "420", "6.21"], "WTW": ["113", "503", "11.97"], "WTW_FILE": "/home/pi/Pictures/2024_08_08/WTW/[5567]14_06_36_right_WTW.png", "AL": [["27.87", "0.20", "11.76"], ["27.88", "0.17", "10.69"], ["27.88", "0.35", "13.05"], ["27.87", "0.27", "12.38"], ["27.90", "0.12", "10.13"], ["27.86", "0.18", "11.25"]], "AL_AVG": ["27.87", "0.19", "11.45"], "IC": [["8.61", "8.53", "8.57"]], "IC_AVG": ["8.57"], "AL/R": "3.26", "VT": "20.61"}
    # }
    # """
    # json_data = json.loads(data)
    # print(json_data)
    # print("")
